<?php
namespace Core;

use Core\HHO;

/**
 * 此类继承 DbHandle 类，目前仅支持 MySQL。
 *
 */
abstract class DbTable extends DbHandle {
	/**
	 * 最近一次执行的SQL语句，此属性仅供外部读取，而修改其值是毫无意义的。
	 * @var string
	 * @access public
	 */
	public $statement = '';
	
	/**
	 * 数据表名，必须为小写，两个单词以一个“_”字符分隔。
	 * @var string
	 * @access protected
	 */
	protected $_table = '';
	/**
	 * 数字类型的字段列表。
	 * @var array
	 * @access protected
	 */
	protected $_numeric = [];

	/**
	 * 初始化数据库操作，初始化数据表映射
	 *
	 * - 注意：
	 * - 若子类中存在 __construct() 方法，则必须在子类的 __construct() 方法中以 parent::__construct() 方式调用此方法
	 */
	protected function __construct() {
		parent::__construct();
		
		$this->_table || $this->_table = strtolower(preg_replace('/([a-z\d])([A-Z])/', '$1_$2', ltrim(strrchr((get_class($this)), '\\'), '\\')));
	}

	/**
	 * 对当前表的指定字段的指定内容进行引起操作。
	 * - $values 参数的默认值不能省略，因为父类中同名方法只有一个参数，省略将导致“Strict standards: Declaration of ... should be compatible with that of ...”错误。
	 *
	 * @param string $fields
	 * @param string|int|float|array|object $values
	 * @return string|int|float|array
	 */
	protected function _quote($fields, $values = NULL) {
		if (in_array($fields, $this->_numeric)) {
			if (is_numeric($values)) {
				return $values;
			}
			
			if (is_scalar($values)) {
				trigger_error("Field '$this->_table.$fields' value expect a number. (Value: {$values})", E_USER_WARNING);
				
				$values += 0;
			} else {
				foreach ($values as &$value) {
					if (!is_numeric($value)) {
						trigger_error("Field '$this->_table.$fields' value expect a number. (Value: {$value})", E_USER_WARNING);
					}
					
					$value += 0;
				}
			}
		} else {
			if (is_scalar($values)) {
				return parent::_quote($values); // $values 肯定不是表达式。
			}
			
			foreach ($values as &$value) {
				$value = parent::_quote($value);
			}
		}
		
		return $values;
	}

	/**
	 * 使用 ` 字符引起指定的字段名
	 *
	 * @param string|array $fields
	 * @param boolean $simple
	 * @return string
	 */
	protected function _backtick($fields, $simple = true) {
		is_string($fields) && $fields = explode(',', str_replace('`', '', $fields));
		
		foreach ($fields as $key => $value) {
			if (preg_match('/^([a-z]\w*)(?:\s+(ASC|DESC))?$/i', trim($value), $v)) {
				$fields[$key] = "`{$v[1]}`" . (isset($v[2]) ? strtoupper(" {$v[2]}") : '');
			} else {
				$fields[$key] = $value;
			}
		}
		
		return implode(',', $fields);
	}

	/**
	 * 组装数据表查询条件表达式，即SQL语句的 WHERE 段
	 *
	 * @param string|array|object $where
	 * @return string
	 */
	protected function _where($where) {
		if (is_array($where) || is_object($where)) {
			$if = array();
			
			foreach ($where as $key => $value) {
				if (is_numeric($key)) {
					$if[] = $value;
				} else {
					$value = $this->_quote($key, $value);
					
					if (is_scalar($value)) {
						$if[] = "`$key`=" . $value;
					} else {
						$if[] = "`$key` IN (" . implode(',', is_array($value) ? $value : (array) $value) . ')';
					}
				}
			}
			
			return ' WHERE ' . implode(' AND ', $if);
		}
		
		return $where ? ' WHERE ' . $where : '';
	}

	/**
	 * 组装结果集查询条件表达式，即SQL语句的 HAVING 段
	 *
	 * @param string|array|object $having
	 * @return string
	 */
	protected function _having($having) {
		if (is_array($having) || is_object($having)) {
			$if = array();
			
			foreach ($having as $key => $value) {
				if (is_numeric($key)) {
					$if[] = $value;
				} else {
					$value = $this->_quote($key, $value);
					
					if (is_scalar($value)) {
						$if[] = "`$key`=" . $value;
					} else {
						$if[] = "`$key` IN(" . implode(',', is_array($value) ? $value : (array) $value) . ')';
					}
				}
			}
			
			return ' HAVING ' . implode(' AND ', $if);
		}
		
		return $having ? ' HAVING ' . $having : '';
	}

	/**
	 * 向当前表中插入（覆盖）一条记录，由 $this->insert() 或 $this->replace() 调用
	 *
	 * @param string $action
	 * @param array|object $data
	 * @param string|array|object $fields
	 * @return int
	 */
	protected function _into($action, $data, $fields) {
		if (is_scalar($data)) {
			return 0;
		}
		
		if ($fields) {
			if (is_string($fields)) {
				$fields = explode(',', $fields);
			} elseif (is_object($fields)) {
				$fields = get_object_vars($fields);
			}
		} else {
			if (is_array($data)) {
				$first = reset($data);
				
				if (is_array($first)) {
					$fields = array_keys($first);
				} elseif (is_object($first)) {
					$fields = array_keys(get_object_vars($first));
				} else {
					$fields = array_keys($data);
				}
			} elseif (is_object($data)) {
				$fields = array_keys(get_object_vars($data));
			} else {
				return 0;
			}
		}
		
		$values = array();
		
		foreach (is_scalar(reset($data)) ? array($data) : $data as $value) {
			$record = array();
			
			foreach ($value as $v) {
				$record[] = $this->_quote($fields[count($record)], $v);
			}
			
			$values[] = '(' . implode(',', $record) . ')';
		}
		
		if (!$this->_execute("$action INTO `{$this->_table}`(" . $this->_backtick($fields) . ') VALUES' . implode(',', $values))) {
			return 0;
		}
		
		return $this->lastid();
	}

	/**
	 * 执行一条SQL，并保存该SQL，若执行SQL产生一个结果集，则返回该结果集对象，否则返回 boolean
	 *
	 * @param string $query
	 * @return DbResult|boolean
	 */
	protected function _execute($query) {
		$this->statement = $query;
		
		return parent::_execute($query);
	}

	/**
	 * 向当前表中插入一条记录，即 MySQL 的 INSERT 语句
	 *
	 * @param array|object $data
	 * @param string|array|object $fields
	 * @return int
	 */
	public function insert($data, $fields = NULL) {
		return $this->_into('INSERT', $data, $fields);
	}

	/**
	 * 向当前表中插入（覆盖）一条记录，即 MySQL 的 REPLACE 语句
	 *
	 * @param array|object $data
	 * @param string|array|object $fields
	 * @return int
	 */
	public function replace($data, $fields = NULL) {
		return $this->_into('REPLACE', $data, $fields);
	}

	/**
	 * 组装 SELECT 语句，$fields 参数值允许出现如 “count(id) as num”、“distinct sum(money) as money” 等复杂的查询
	 *
	 * @param array|object|string $where
	 * @param string|array $orderby
	 * @param string|int $limit
	 * @param string|array $fields
	 * @param string|array $groupby
	 * @param string|object|string $having
	 * @return string
	 */
	public function prepare($where = null, $orderby = null, $limit = null, $fields = null, $groupby = null, $having = null) {
		if (!$fields) {
			$fields = '*';
		} elseif (is_array($fields)) {
			foreach ($fields as $key => $value) {
				$value = trim($value);
				$value = preg_match('/^(\w+)(\s.+)?$/', $value, $v) ? "`$v[1]`" . (isset($v[2]) ? ' ' . trim($v[2]) : '') : $value;
				$value = preg_match('/^(.+)\s+AS\s+(\w+)$/i', $value, $v) ? "{$v[1]} AS `{$v[2]}`" : $value;
			}
			
			$fields = implode(',', $fields);
		}
		
		$groupby = $groupby ? ' GROUP BY ' . $this->_backtick($groupby) : '';
		
		$orderby = $orderby ? ' ORDER BY ' . $this->_backtick($orderby) : '';
		
		$limit = $limit ? ' LIMIT ' . strtoupper($limit) : '';
		
		return 'SELECT ' . $fields . " FROM `{$this->_table}`" . $this->_where($where) . $groupby . $this->_having($having) . $orderby . $limit;
	}

	/**
	 * 在当前表中查询并返回一个结果集，各参数介绍见 self::prepare() 同名参数
	 *
	 * @param array|object|string $where
	 * @param string|array $orderby
	 * @param string|int $limit
	 * @param string|array $fields
	 * @param string|array $groupby
	 * @param string|object|string $having
	 * @return DbResult|false
	 */
	public function select($where = null, $orderby = null, $limit = null, $fields = null, $groupby = null, $having = null) {
		return $this->_execute($this->prepare($where, $orderby, $limit, $fields, $groupby, $having));
	}

	/**
	 * 获得指定条件的记录数量。
	 *
	 * @param array|object|string $where
	 * @param string|array $groupby
	 * @param string|object|string $having
	 * @return int|FALSE
	 */
	public function count($where = null, $groupby = null, $having = null) {
		$result = $this->_execute($this->prepare($where, NULL, NULL, 'COUNT(*) AS rows', $groupby, $having));
		
		if (isset($result[0]['rows'])) {
			return intval($result[0]['rows']);
		}
		
		return FALSE;
	}

	/**
	 * 在当前表中随机查询并返回一个结果集，各参数介绍见 self::prepare() 同名参数
	 *
	 * @param string|int $limit
	 * @param array|object|string $where
	 * @param string|array $orderby
	 * @param string|array $fields
	 * @param string|array $groupby
	 * @param string|object|string $having
	 * @return DbResult|false
	 */
	public function random($limit = null, $where = null, $orderby = null, $fields = null, $groupby = null, $having = null) {
		$statement = $this->prepare($where, 'RANDOM()', $limit, $fields, $groupby, $having);
		
		if ($orderby) {
			$statement = 'SELECT * FROM (' . $statement . ') ORDER BY ' . $this->_backtick($orderby);
		}
		
		return $this->_execute($statement);
	}

	/**
	 * 更新当前表里的记录，即 MySQL 的 UPDATE 语句
	 *
	 * 注意：
	 * $data 中的字段数据类型若非数字类型，则对应的字段值禁止出现运算表达式，详见 $this->_quote() 方法
	 *
	 * @param string|array|object $data
	 * @param string|array|object $where
	 * @param string|array $orderby
	 * @param int $limit
	 * @return int|false
	 */
	public function update($data, $where = null, $orderby = null, $limit = null) {
		$orderby = $orderby ? ' ORDER BY ' . $this->_backtick($orderby) : '';
		
		$limit = $limit ? ' LIMIT ' . strtoupper($limit) : '';
		
		if (is_array($data) || is_object($data)) {
			$fields = array();
			
			foreach ($data as $key => $value) {
				$fields[] = is_numeric($key) ? $value : "`$key`=" . $this->_quote($key, $value);
			}
			
			$update = implode(',', $fields);
		} else {
			$update = &$data;
		}
		
		if (!$this->_execute("UPDATE `{$this->_table}` SET $update" . $this->_where($where) . $orderby . $limit)) {
			return false;
		}
		
		return $this->affect;
	}

	/**
	 * 删除当前表里满足条件的记录，即 MySQL 的 DELETE 语句
	 *
	 * @param string|array|object $where
	 * @param string|array $orderby
	 * @param int $limit
	 * @return int|false
	 */
	public function delete($where = null, $orderby = null, $limit = null) {
		$orderby = $orderby ? ' ORDER BY ' . $this->_backtick($orderby) : '';
		
		$limit = $limit ? ' LIMIT ' . strtoupper($limit) : '';
		
		if (!$this->_execute("DELETE FROM `{$this->_table}`" . $this->_where($where) . $orderby . $limit)) {
			return false;
		}
		
		return $this->affect;
	}

	/**
	 * 获得当前表的一个或多个字段的数据类型以及数据格式的相关信息，注意：此方法所获得的信息不足以创建表
	 *
	 * @param string|array $fields
	 * @return array
	 */
	public function describe($fields = array()) {
		static $history = [];
		
		$fieldInfo = [];
		
		if (isset($history[$this->_table])) {
			if (!$fields) {
				return $history[$this->_table];
			} elseif (is_string($fields)) {
				return $history[$this->_table][trim(trim($fields), '`')];
			}
			
			foreach ($fields as $item) {
				$item = trim(trim($item), '`');
				$fieldInfo[$item] = $history[$this->_table][$item];
			}
			
			return $fieldInfo;
		}
		
		$result = $this->_execute("DESCRIBE `{$this->_table}`");
		if (!$result) {
			return [];
		}
		
		foreach ($result as $value) {
			if (preg_match('/^(\w+)\((\d+)()\)\s?(\w*)$/', $value['Type'], $fieldType)) {
				// void
			} elseif (preg_match('/^(\w+)(\((\d+),(\d+)\))?\s?(\w*)$/', $value['Type'], $type)) {
				switch ($type[1]) {
					case 'tinytext' :
						$fieldType = array(1 => 'tinytext', 255);
						break;
					case 'text' :
						$fieldType = array(1 => 'text', 65535);
						break;
					case 'mediumtext' :
						$fieldType = array(1 => 'mediumtext', 16777215);
						break;
					case 'decimal' :
						$fieldType = array(1 => 'decimal', $type[3], $type[4], $type[5]);
						break;
					case 'float' :
						$fieldType = array(1 => 'float', $type[3], $type[4], $type[5]);
						break;
					case 'timestamp' :
						$fieldType = array(1 => 'timestamp', 19);
						break;
					case 'date' :
						$fieldType = array(1 => 'date', 11);
						break;
					default :
						trigger_error("Class 'DbTable' unknown data type. (Field: $this->_table.{$value['Field']}, Type: {$type[1]})", E_USER_WARNING);
						continue 2;
				}
			} elseif (substr($value['Type'], 0, 4) == 'enum') {
				$x = explode(',', substr($value['Type'], 5, -1));
				foreach ($x as &$v) {
					$v = trim($v, "'");
				}
				unset($v);
				
				$fieldType = array(1 => 'enum', NULL, NULL, NULL, $x);
			} else {
				preg_match('/^(\w+)\W?/', $value['Type'], $type);
				trigger_error("Class 'DbTable' unknown data type. (Field: $this->_table.{$value['Field']}, Type: {$type[1]})", E_USER_WARNING);
				continue;
			}
			
			$fieldInfo[$value['Field']]['type'] = $fieldType[1]; // fields.type：字段的数据类型
			$fieldInfo[$value['Field']]['length'] = isset($fieldType[2]) ? (int) $fieldType[2] : NULL; // fields.length：字段的所能接受的最大文本长度
			$fieldInfo[$value['Field']]['decimal'] = isset($fieldType[3]) && $fieldType[3] !== '' ? (int) $fieldType[3] : NULL;
			$fieldInfo[$value['Field']]['unsigned'] = isset($fieldType[4]) ? $fieldType[4] == 'unsigned' : NULL;
			$fieldInfo[$value['Field']]['values'] = isset($fieldType[5]) ? $fieldType[5] : NULL;
			$fieldInfo[$value['Field']]['null'] = ($value['Null'] == 'YES');
			$fieldInfo[$value['Field']]['default'] = ($value['Default'] == 'NULL' ? NULL : $value['Default']);
			$fieldInfo[$value['Field']]['primary'] = ($value['Key'] == 'PRI');
			$fieldInfo[$value['Field']]['autoIncrement'] = ($value['Extra'] == 'auto_increment');
			$fieldInfo[$value['Field']]['onUpdateCurrentTimestamp'] = ($value['Extra'] == 'on update CURRENT_TIMESTAMP');
		}
		
		if (!$fieldInfo) {
			return [];
		}
		
		$history[$this->_table] = $fieldInfo;
		
		return $this->describe($fields);
	}

	/**
	 * 表映射类的单实例模式实现。
	 *
	 * @return Object
	 */
	public static function getInstance() {
		static $instance = array();
		
		$class = get_called_class();
		
		if (!isset($instance[$class])) {
			$instance[$class] = new static();
		}
		
		return $instance[$class];
	}
}